# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:        博智科技  
# Name:         demo1461
# Description:
# Author:       yzl
# Date:         2019-02-10
#-------------------------------------------------------------------------------


from sqlalchemy import create_engine,MetaData,Table,Column,Integer,String,Float,exc,orm
from sqlalchemy.ext.declarative import declarative_base

# 连接协议    数据库库包    用户名:密码@服务:端口/数据库名?字符集
mysql = 'mysql+pymysql://root:1@127.0.0.1:8889/testdb?charset=utf8'
# tablename = 'persons1'

# 创建数据库引擎
engine = create_engine(mysql,encoding='utf-8')
# 引擎连接数据
# engine.connect()

# metadata = MetaData(engine)

"""
person = Table(tablename,metadata,
               Column('id',Integer,primary_key= True),
               Column('name',String(30)),
               Column('age',Integer),
               Column('address',String(100)),
               Column('salary',Float))
metadata.create_all(engine)
print('---over---')
"""

Base = declarative_base()


# 数据表类 o实体类
class Person(Base):
    # 声明表名
    __tablename__ = 'persons1'

    id = Column('id',Integer,primary_key= True) # 主键列
    name = Column('name',String(30)) # varchar(30)
    age = Column('age',Integer)
    address = Column('address',String(100))
    salary = Column('salary',Float) # 浮点类型

    def __str__(self):
        return '<id={},name={},age={},address={},salary={}>'.format(self.id,self.name,self.age,self.address,self.salary)

    def __repr__(self):
        return self.__str__()

# 创建表
# Base.metadata.create_all(engine)
# print('ok')
# 创建 session
Session = orm.sessionmaker(bind=engine)
# 实例化 session
session = Session()



# 删除数据
# session.query(Person).delete()
# session.commit()
# print('delete ok')


"""
# 实例数据 对应的就是数据库表中的一行
person1 = Person(id=11,name='张三',age=30,address='长沙',salary=10000)
print(person1)
person2 = Person(id=21,name='李四',age=31,address='株洲',salary=9000)
person3 = Person(id=31,name='王五',age=31,address='湘潭',salary=8000)

# 新增数据
session.add(person1)
session.add(person2)
session.add(person3)

# 提交事务
session.commit()
print('新增数据成功')
"""

"""
# 修改数据
# select * from persons1 where name='李四' limit 1
p = session.query(Person).filter(Person.name =='李四').first()
p.address = '株洲市天元区'
session.add(p)
session.commit()
print('修改李四 地址 成功')


"""

"""
p = session.query(Person).filter(Person.name=='李四').first()
print(p.address)


"""
ps = session.query(Person).filter(Person.name=='李四').filter(Person.address=='株洲').all()

for p in ps:
    print(p.name)

session.close()
